﻿using DeviceDebugger.Model;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DeviceDebugger.DataAccess
{
    public class DataVisitor
    {
        static DbUtility db = new DbUtility(DbProviderType.SQLite);

        #region 堆垛机信息
        /// <summary>
        /// 添加堆垛机
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int AddStacker(StackerModel model)
        {
            object result = 0;
            List<DbParameter> listParams = new();
            string ssql = " insert into Stacker values(null,$Name,$IPAdress,$LanewayNo,$RowCount,$ColumCount,$LayerCount);select last_insert_rowid();";

            //插入值均为手动输入，参数化sql防注入
            listParams.Add(db.CreateDbParameter("$Name", model.Name));
            listParams.Add(db.CreateDbParameter("$IPAdress", model.IPAdress));
            listParams.Add(db.CreateDbParameter("$LanewayNo", model.LanewayNo));
            listParams.Add(db.CreateDbParameter("$RowCount", model.RowCount));
            listParams.Add(db.CreateDbParameter("$ColumCount", model.ColumCount));
            listParams.Add(db.CreateDbParameter("$LayerCount", model.LayerCount));

            try
            {
                result = db.ExecuteScalar(ssql, listParams);
            }
            catch (Exception ex)
            {
            }

            return Convert.ToInt32(result);
        }

        /// <summary>
        /// 修改堆垛机
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool EditStacker(StackerModel model)
        {
            bool bResult = false;
            List<DbParameter> listParams = new();
            string ssql = " update Stacker set Name=$Name,IPAdress=$IPAdress,LanewayNo=$LanewayNo,RowCount=$RowCount,ColumCount=$ColumCount, LayerCount=$LayerCount where Id=$Id ";
            listParams.Add(db.CreateDbParameter("$Id", model.Id));
            //插入值均为手动输入，参数化sql防注入
            listParams.Add(db.CreateDbParameter("$Name", model.Name));
            listParams.Add(db.CreateDbParameter("$IPAdress", model.IPAdress));
            listParams.Add(db.CreateDbParameter("$LanewayNo", model.LanewayNo));
            listParams.Add(db.CreateDbParameter("$RowCount", model.RowCount));
            listParams.Add(db.CreateDbParameter("$ColumCount", model.ColumCount));
            listParams.Add(db.CreateDbParameter("$LayerCount", model.LayerCount));

            bResult = db.ExecuteNonQuery(ssql, listParams) > 0;
            return bResult;
        }

        /// <summary>
        /// 删除堆垛机信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool DeleteStacker(StackerModel model)
        {
            return DeleteStacker(model.Id);
        }
        /// <summary>
        /// 删除堆垛机信息
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static bool DeleteStacker(Int64 Id)
        {
            string ssql = string.Format("delete from Stacker where Id={0}", Id);
            return db.ExecuteNonQuery(ssql, null) > 0;
        }

        /// <summary>
        /// 查询所有堆垛机
        /// </summary>
        /// <returns></returns>
        public static List<StackerModel> GetStackers()
        {
            string ssql = "select * from Stacker";
            return DataHelper.DataTableToList<StackerModel>(db.ExecuteDataTable(ssql, null));
        }
        #endregion

        #region 计划作业
        /// <summary>
        /// 批量插入计划作业
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool AddStackerTasks(List<StackerTasksModel> list)
        {
            bool bResult = false;
            List<string> listSql = new();
            string ssql = "Insert Into StackerTasks values(null,'{0}','{1}','{2}',true,false,false) ";
            list.ForEach(a =>
            {
                listSql.Add(string.Format(ssql, a.StackerId, a.StartLocation, a.EndLocation));
            });

            try
            {
                //用事务提交插入语句
                bResult = db.ExecuteNonQueryTransaction(listSql);
            }
            catch (Exception ex)
            {
                throw new Exception("添加失败！数据库异常");
            }

            return bResult;
        }

        /// <summary>
        /// 插入计划作业
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static int AddStackerTasks(StackerTasksModel model)
        {
            object iResult = 0;
            List<DbParameter> listParams = new();
            string ssql = "Insert Into StackerTasks values(null,$StackerId,$StartLocation,$EndLocation,true,false,false);select last_insert_rowid();";
            listParams.Add(db.CreateDbParameter("$StackerId", model.StackerId));
            listParams.Add(db.CreateDbParameter("$StartLocation", model.StartLocation));
            listParams.Add(db.CreateDbParameter("$EndLocation", model.EndLocation));

            try
            {
                iResult = db.ExecuteScalar(ssql, listParams);
            }
            catch (Exception ex)
            {
                throw new Exception("添加失败！数据库异常");
            }

            return int.Parse(iResult.ToString());
        }

        /// <summary>
        /// 查询堆垛机任务
        /// </summary>
        /// <returns></returns>
        public static List<StackerTasksModel> GetStackerTasks(Int64 StackerId, bool? IsAutoCreate = true, bool? IsExecute = false)
        {
            string ssql = string.Format("select * from StackerTasks where StackerId={0} ", StackerId);
            if (IsAutoCreate == true)
                ssql += " and IsAutoCreate=true ";
            else
                ssql += " and IsAutoCreate=false ";

            if (IsExecute == true)
                ssql += "and IsExecute=true ";
            else
                ssql += "and IsExecute=false ";

            ssql += " order by Id";

            return DataHelper.DataTableToList<StackerTasksModel>(db.ExecuteDataTable(ssql, null));
        }

        /// <summary>
        /// 查询堆垛机任务总数
        /// </summary>
        /// <param name="StackerId"></param>
        /// <param name="IsAutoCreate"></param>
        /// <param name="IsExecute"></param>
        /// <returns></returns>
        public static int GetStackerTasksCount(Int64 StackerId, bool? IsAutoCreate = true, bool? IsExecute = false)
        {
            return GetStackerTasks(StackerId, IsAutoCreate, IsExecute).Count();
        }

        /// <summary>
        /// 清空计划作业
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static bool DeleteTask(Int64 StackerId)
        {
            string ssql = string.Format("delete from StackerTasks where IsAutoCreate=true and StackerId={0}", StackerId);
            return db.ExecuteNonQuery(ssql, null) >= 0;
        }

        /// <summary>
        /// 修改计划任务
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool EditTask(StackerTasksModel model)
        {
            List<DbParameter> listParams = new();
            string ssql = " update StackerTasks set IsExecute=$IsExecute,IsMalfunction=$IsMalfunction where Id=$Id ";
            listParams.Add(db.CreateDbParameter("$IsExecute", model.IsExecute));
            listParams.Add(db.CreateDbParameter("$IsMalfunction", model.IsMalfunction));
            listParams.Add(db.CreateDbParameter("$Id", model.Id));
            return db.ExecuteNonQuery(ssql, listParams) > 0;
        }
        #endregion

        #region PLC通讯协议

        /// <summary>
        /// 查询协议点信息
        /// </summary>
        /// <returns></returns>
        public static List<ProtocolModel> GetProtocols()
        {
            string ssql = "select * from Protocol";
            return DataHelper.DataTableToList<ProtocolModel>(db.ExecuteDataTable(ssql, null));
        }

        /// <summary>
        /// 修改堆垛机
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static bool EditProtocol(ProtocolModel model)
        {
            bool bResult = false;
            List<DbParameter> listParams = new();
            string ssql = " update Protocol set IsDisplay=$IsDisplay, PLC_Adress=$PLC_Adress,OPC_Adress=$OPC_Adress,Description=$Description where Id=$Id ";
            listParams.Add(db.CreateDbParameter("$Id", model.Id));
            //插入值均为手动输入，参数化sql防注入
            listParams.Add(db.CreateDbParameter("$IsDisplay", model.IsDisplay));
            listParams.Add(db.CreateDbParameter("$PLC_Adress", model.PLC_Adress));
            listParams.Add(db.CreateDbParameter("$OPC_Adress", model.OPC_Adress));
            listParams.Add(db.CreateDbParameter("$Description", model.Description));

            bResult = db.ExecuteNonQuery(ssql, listParams) > 0;
            return bResult;
        }

        #endregion

        #region 禁用货位

        /// <summary>
        /// 添加禁用货位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int AddStorageDisable(StorageDisableModel model)
        {
            object iResult = 0;
            List<DbParameter> listParams = new();
            string ssql = " insert into StorageDisable values(null,$StackerId,$Location);select last_insert_rowid(); ";

            //插入值均为手动输入，参数化sql防注入
            listParams.Add(db.CreateDbParameter("$StackerId", model.StackerId));
            listParams.Add(db.CreateDbParameter("$Location", model.Location));

            try
            {
                iResult = db.ExecuteScalar(ssql, listParams);
            }
            catch (Exception)
            {
            }

            return Convert.ToInt32(iResult);
        }

        /// <summary>
        /// 移除禁用货位
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public static bool DeleteStorageDisable(Int64 Id)
        {
            string ssql = string.Format("delete from StorageDisable where Id={0}", Id);
            return db.ExecuteNonQuery(ssql, null) > 0;
        }

        /// <summary>
        /// 获取每个堆垛机的禁用货位
        /// </summary>
        /// <param name="StackerId"></param>
        /// <returns></returns>
        public static List<StorageDisableModel> GetStorageDisables(Int64 StackerId)
        {
            string ssql = string.Format("select * from StorageDisable where StackerId={0} ", StackerId);
            return DataHelper.DataTableToList<StorageDisableModel>(db.ExecuteDataTable(ssql, null));
        }

        #endregion

        #region 运行日志
        /// <summary>
        /// 插入运行日志
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static bool AddRunLogs(RunLogs model)
        {
            bool bResult = false;
            List<DbParameter> listParams = new();
            string ssql = "Insert Into RunLogs values(null,$StackerId,$HappenTime,$LogsText,$LogsType) ";
            listParams.Add(db.CreateDbParameter("$StackerId", model.StackerId));
            listParams.Add(db.CreateDbParameter("$HappenTime", model.HappenTime));
            listParams.Add(db.CreateDbParameter("$LogsText", model.LogsText));
            listParams.Add(db.CreateDbParameter("$LogsType", (int)model.LogsType));

            try
            {
                //用事务提交插入语句
                bResult = db.ExecuteNonQuery(ssql, listParams) > 0;
            }
            catch (Exception ex)
            {
                throw new Exception("添加失败！数据库异常");
            }

            return bResult;
        }

        /// <summary>
        /// 获取每个堆垛机的运行日志
        /// </summary>
        /// <param name="StackerId"></param>
        /// <returns></returns>
        public static List<StorageDisableModel> GetRunLogs(Int64 StackerId)
        {
            string ssql = string.Format("select * from RunLogs where StackerId={0} ", StackerId);
            return DataHelper.DataTableToList<StorageDisableModel>(db.ExecuteDataTable(ssql, null));
        }
        #endregion

        #region 故障信息
        /// <summary>
        /// 获取配置的故障信息
        /// </summary>
        /// <param name="StackerId"></param>
        /// <returns></returns>
        public static List<TroubleConfigModel> GetTroubleConfig()
        {
            string ssql = string.Format("select * from TroubleConfig");
            return DataHelper.DataTableToList<TroubleConfigModel>(db.ExecuteDataTable(ssql, null));
        }

        #endregion
    }
}
